<!DOCTYPE html>
<html>
<head><meta name="generator" content="Hexo 3.9.0">
    

    

    



    <meta charset="utf-8">
    
    
    
    <title>mybatis动态sql语句 | 奔跑的蜗牛 | 虚心若愚</title>
    <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
    
    <meta name="theme-color" content="#3F51B5">
    
    
    <meta name="keywords" content="mybatis,ORM">
    <meta name="description" content="概述mybatis的动态sql语句是基于OGNL表达式的。可以方便的在sql语句中实现某些逻辑，总体来说mybatis动态sql语句主要有一下几类。  if语句（简单的条件判断） choose(when,otherwise)，相当于java语言中的switch,与jstl中的choose很类似。 trim（对包含的内容加上prefix，或者suffix等,前缀，后缀） where（主要是用来贱货s">
<meta name="keywords" content="mybatis,ORM">
<meta property="og:type" content="article">
<meta property="og:title" content="mybatis动态sql语句">
<meta property="og:url" content="http://windynature.oschina.io/2016/12/23/java/mybatis动态sql语句/index.html">
<meta property="og:site_name" content="奔跑的蜗牛">
<meta property="og:description" content="概述mybatis的动态sql语句是基于OGNL表达式的。可以方便的在sql语句中实现某些逻辑，总体来说mybatis动态sql语句主要有一下几类。  if语句（简单的条件判断） choose(when,otherwise)，相当于java语言中的switch,与jstl中的choose很类似。 trim（对包含的内容加上prefix，或者suffix等,前缀，后缀） where（主要是用来贱货s">
<meta property="og:locale" content="zh-CN">
<meta property="og:updated_time" content="2020-03-18T04:07:24.366Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="mybatis动态sql语句">
<meta name="twitter:description" content="概述mybatis的动态sql语句是基于OGNL表达式的。可以方便的在sql语句中实现某些逻辑，总体来说mybatis动态sql语句主要有一下几类。  if语句（简单的条件判断） choose(when,otherwise)，相当于java语言中的switch,与jstl中的choose很类似。 trim（对包含的内容加上prefix，或者suffix等,前缀，后缀） where（主要是用来贱货s">
    
        <link rel="alternative" href="/atom.xml" title="奔跑的蜗牛" type="application/atom+xml">
    
    <link rel="shortcut icon" href="/blog/favicon.ico">
    <link rel="stylesheet" href="//unpkg.com/hexo-theme-material-indigo@1.5.2/css/style.css">
    <script>window.lazyScripts=[]</script>
</head>

<body>
    <div id="loading" class="active"></div>

    <aside id="menu" class="hide" >
  <div class="inner flex-row-vertical">
    <a href="javascript:;" class="header-icon waves-effect waves-circle waves-light" id="menu-off">
        <i class="icon icon-lg icon-close"></i>
    </a>
    <div class="brand-wrap" style="background-image:url(/blog/img/timg.jpg)">
      <div class="brand">
        <a href="/blog/" class="avatar waves-effect waves-circle waves-light">
          <img src="/blog/img/user2.jpg">
        </a>
        <hgroup class="introduce">
          <h5 class="nickname">windy</h5>
          <a href="mailto:windynature@163.com" title="windynature@163.com" class="mail">windynature@163.com</a>
        </hgroup>
      </div>
    </div>
    <div class="scroll-wrap flex-col">
      <ul class="nav">
        
            <li class="waves-block waves-effect">
              <a href="/blog/"  >
                <i class="icon icon-lg icon-home"></i>
                主页
              </a>
            </li>
        
            <li class="waves-block waves-effect">
              <a href="/blog/archives"  >
                <i class="icon icon-lg icon-archives"></i>
                归档
              </a>
            </li>
        
            <li class="waves-block waves-effect">
              <a href="/blog/tags"  >
                <i class="icon icon-lg icon-tags"></i>
                标签
              </a>
            </li>
        
            <li class="waves-block waves-effect">
              <a href="/blog/categories"  >
                <i class="icon icon-lg icon-th-list"></i>
                分类
              </a>
            </li>
        
            <li class="waves-block waves-effect">
              <a href="https://github.com/WindyNature" target="_blank" >
                <i class="icon icon-lg icon-github"></i>
                Github
              </a>
            </li>
        
            <li class="waves-block waves-effect">
              <a href="https://git.oschina.net/windynature" target="_blank" >
                <i class="icon icon-lg icon-mayun"></i>
                码云
              </a>
            </li>
        
      </ul>
    </div>
  </div>
</aside>

    <main id="main">
        <header class="top-header" id="header">
    <div class="flex-row">
        <a href="javascript:;" class="header-icon waves-effect waves-circle waves-light on" id="menu-toggle">
          <i class="icon icon-lg icon-navicon"></i>
        </a>
        <div class="flex-col header-title ellipsis">mybatis动态sql语句</div>
        
        <div class="search-wrap" id="search-wrap">
            <a href="javascript:;" class="header-icon waves-effect waves-circle waves-light" id="back">
                <i class="icon icon-lg icon-chevron-left"></i>
            </a>
            <input type="text" id="key" class="search-input" autocomplete="off" placeholder="输入感兴趣的关键字">
            <a href="javascript:;" class="header-icon waves-effect waves-circle waves-light" id="search">
                <i class="icon icon-lg icon-search"></i>
            </a>
        </div>
        
        
        <a href="javascript:;" class="header-icon waves-effect waves-circle waves-light" id="menuShare">
            <i class="icon icon-lg icon-share-alt"></i>
        </a>
        
    </div>
</header>
<header class="content-header post-header">

    <div class="container fade-scale">
        <h1 class="title">mybatis动态sql语句</h1>
        <h5 class="subtitle">
            
                <time datetime="2016-12-23T02:24:09.000Z" itemprop="datePublished" class="page-time">
  2016-12-23
</time>


	<ul class="article-category-list"><li class="article-category-list-item"><a class="article-category-list-link" href="/blog/categories/mybatis/">mybatis</a><ul class="article-category-list-child"><li class="article-category-list-item"><a class="article-category-list-link" href="/blog/categories/mybatis/数据库/">数据库</a></li></ul></li></ul>

            
        </h5>
    </div>

    

</header>


<div class="container body-wrap">
    
    <aside class="post-widget">
        <nav class="post-toc-wrap" id="post-toc">
            <h4>TOC</h4>
            <ol class="post-toc"><li class="post-toc-item post-toc-level-2"><a class="post-toc-link" href="#概述"><span class="post-toc-text">概述</span></a></li><li class="post-toc-item post-toc-level-2"><a class="post-toc-link" href="#if-语句"><span class="post-toc-text">if 语句</span></a></li><li class="post-toc-item post-toc-level-2"><a class="post-toc-link" href="#choose-when-otherwize-语句"><span class="post-toc-text">choose (when,otherwize)语句</span></a></li><li class="post-toc-item post-toc-level-2"><a class="post-toc-link" href="#trim语句"><span class="post-toc-text">trim语句</span></a></li><li class="post-toc-item post-toc-level-2"><a class="post-toc-link" href="#where语句"><span class="post-toc-text">where语句</span></a></li><li class="post-toc-item post-toc-level-2"><a class="post-toc-link" href="#set语句"><span class="post-toc-text">set语句</span></a></li><li class="post-toc-item post-toc-level-2"><a class="post-toc-link" href="#foreach"><span class="post-toc-text">foreach</span></a><ol class="post-toc-child"><li class="post-toc-item post-toc-level-3"><a class="post-toc-link" href="#单参数List的类型"><span class="post-toc-text">单参数List的类型</span></a></li><li class="post-toc-item post-toc-level-3"><a class="post-toc-link" href="#数组类型的参数"><span class="post-toc-text">数组类型的参数</span></a></li><li class="post-toc-item post-toc-level-3"><a class="post-toc-link" href="#Map-类型的参数"><span class="post-toc-text">Map 类型的参数</span></a></li></ol></li></ol>
        </nav>
    </aside>
    
<article id="post-java/mybatis动态sql语句"
  class="post-article article-type-post fade" itemprop="blogPost">

    <div class="post-card">
        <h1 class="post-card-title">mybatis动态sql语句</h1>
        <div class="post-meta">
            <time class="post-time" title="2016年12月23日 10:24" datetime="2016-12-23T02:24:09.000Z"  itemprop="datePublished">2016-12-23</time>

            
	<ul class="article-category-list"><li class="article-category-list-item"><a class="article-category-list-link" href="/blog/categories/mybatis/">mybatis</a><ul class="article-category-list-child"><li class="article-category-list-item"><a class="article-category-list-link" href="/blog/categories/mybatis/数据库/">数据库</a></li></ul></li></ul>



            
<span id="busuanzi_container_page_pv" title="文章总阅读量" style='display:none'>
    <i class="icon icon-eye icon-pr"></i><span id="busuanzi_value_page_pv"></span>
</span>


            

        </div>
        <div class="post-content" id="post-content" itemprop="postContent">
            <h2 id="概述"><a href="#概述" class="headerlink" title="概述"></a>概述</h2><p>mybatis的动态sql语句是基于OGNL表达式的。可以方便的在sql语句中实现某些逻辑，总体来说mybatis动态sql语句主要有一下几类。</p>
<ol>
<li><code>if</code>语句（简单的条件判断）</li>
<li><code>choose(when,otherwise)</code>，相当于java语言中的switch,与jstl中的choose很类似。</li>
<li><code>trim</code>（对包含的内容加上prefix，或者suffix等,前缀，后缀）</li>
<li><code>where</code>（主要是用来贱货sql语句中where条件判断的，能智能的处理and or，不必担心多余导致语法错误）</li>
<li><code>set</code>（主要用户更新时）</li>
<li><code>foreach</code>（在实现mybatis in语句查询时特别有用）</li>
</ol>
<hr>
<p>[toc]</p>
<h2 id="if-语句"><a href="#if-语句" class="headerlink" title="if 语句"></a>if 语句</h2><figure class="highlight xml"><table><tr><td class="code"><pre><span class="line"><span class="tag">&lt;<span class="name">select</span> <span class="attr">id</span>=<span class="string">"dynamicIfTest"</span> <span class="attr">parameterType</span>=<span class="string">"Blog"</span> <span class="attr">resultType</span>=<span class="string">"Blog"</span>&gt;</span></span><br><span class="line">    select * from t_blog where 1 = 1</span><br><span class="line">    <span class="tag">&lt;<span class="name">if</span> <span class="attr">test</span>=<span class="string">"title != null"</span>&gt;</span></span><br><span class="line">        and title = #&#123;title&#125;</span><br><span class="line">    <span class="tag">&lt;/<span class="name">if</span>&gt;</span></span><br><span class="line">    <span class="tag">&lt;<span class="name">if</span> <span class="attr">test</span>=<span class="string">"content != null"</span>&gt;</span></span><br><span class="line">        and content = #&#123;content&#125;</span><br><span class="line">    <span class="tag">&lt;/<span class="name">if</span>&gt;</span></span><br><span class="line">    <span class="tag">&lt;<span class="name">if</span> <span class="attr">test</span>=<span class="string">"owner != null"</span>&gt;</span></span><br><span class="line">        and owner = #&#123;owner&#125;</span><br><span class="line">    <span class="tag">&lt;/<span class="name">if</span>&gt;</span></span><br><span class="line"><span class="tag">&lt;/<span class="name">select</span>&gt;</span></span><br></pre></td></tr></table></figure>

<p>这条语句的意思非常简单，如果你提供了title参数，那么就要满足title=#{title}，同样如果你提供了Content和Owner的时候，它们也需要满足相应的条件，之后就是返回满足这些条件的所有Blog，这是非常有用的一个功能，以往我们使用其他类型框架或者直接使用JDBC的时候， 如果我们要达到同样的选择效果的时候，我们就需要拼SQL语句，这是极其麻烦的，比起来，上述的动态SQL就要简单多了</p>
<h2 id="choose-when-otherwize-语句"><a href="#choose-when-otherwize-语句" class="headerlink" title="choose (when,otherwize)语句"></a>choose (when,otherwize)语句</h2><figure class="highlight xml"><table><tr><td class="code"><pre><span class="line"><span class="tag">&lt;<span class="name">select</span> <span class="attr">id</span>=<span class="string">"dynamicChooseTest"</span> <span class="attr">parameterType</span>=<span class="string">"Blog"</span> <span class="attr">resultType</span>=<span class="string">"Blog"</span>&gt;</span></span><br><span class="line">    select * from t_blog where 1 = 1 </span><br><span class="line">    <span class="tag">&lt;<span class="name">choose</span>&gt;</span></span><br><span class="line">        <span class="tag">&lt;<span class="name">when</span> <span class="attr">test</span>=<span class="string">"title != null"</span>&gt;</span></span><br><span class="line">            and title = #&#123;title&#125;</span><br><span class="line">        <span class="tag">&lt;/<span class="name">when</span>&gt;</span></span><br><span class="line">        <span class="tag">&lt;<span class="name">when</span> <span class="attr">test</span>=<span class="string">"content != null"</span>&gt;</span></span><br><span class="line">            and content = #&#123;content&#125;</span><br><span class="line">        <span class="tag">&lt;/<span class="name">when</span>&gt;</span></span><br><span class="line">        <span class="tag">&lt;<span class="name">otherwise</span>&gt;</span></span><br><span class="line">            and owner = "owner1"</span><br><span class="line">        <span class="tag">&lt;/<span class="name">otherwise</span>&gt;</span></span><br><span class="line">    <span class="tag">&lt;/<span class="name">choose</span>&gt;</span></span><br><span class="line"><span class="tag">&lt;/<span class="name">select</span>&gt;</span></span><br></pre></td></tr></table></figure>

<p><code>when</code>元素表示当<code>when</code>中的条件满足的时候就输出其中的内容，跟JAVA中的switch效果差不多的是按照条件的顺序，<code>当when中有条件满足的时候，就会跳出choose</code>，即<code>所有的when和otherwise条件中，只有一个会输出</code>，<code>当所有的条件都不满足的时候就输出otherwise中的内容</code>。所以上述语句的意思非常简单， 当title!=null的时候就输出and titlte = #{title}，不再往下判断条件，当title为空且content!=null的时候就输出and content = #{content}，当所有条件都不满足的时候就输出otherwise中的内容。</p>
<h2 id="trim语句"><a href="#trim语句" class="headerlink" title="trim语句"></a>trim语句</h2><figure class="highlight xml"><table><tr><td class="code"><pre><span class="line"><span class="tag">&lt;<span class="name">select</span> <span class="attr">id</span>=<span class="string">"dynamicTrimTest"</span> <span class="attr">parameterType</span>=<span class="string">"Blog"</span> <span class="attr">resultType</span>=<span class="string">"Blog"</span>&gt;</span></span><br><span class="line">    select * from t_blog </span><br><span class="line">    <span class="tag">&lt;<span class="name">trim</span> <span class="attr">prefix</span>=<span class="string">"where"</span> <span class="attr">prefixOverrides</span>=<span class="string">"and |or"</span>&gt;</span></span><br><span class="line">        <span class="tag">&lt;<span class="name">if</span> <span class="attr">test</span>=<span class="string">"title != null"</span>&gt;</span></span><br><span class="line">            title = #&#123;title&#125;</span><br><span class="line">        <span class="tag">&lt;/<span class="name">if</span>&gt;</span></span><br><span class="line">        <span class="tag">&lt;<span class="name">if</span> <span class="attr">test</span>=<span class="string">"content != null"</span>&gt;</span></span><br><span class="line">            and content = #&#123;content&#125;</span><br><span class="line">        <span class="tag">&lt;/<span class="name">if</span>&gt;</span></span><br><span class="line">        <span class="tag">&lt;<span class="name">if</span> <span class="attr">test</span>=<span class="string">"owner != null"</span>&gt;</span></span><br><span class="line">            or owner = #&#123;owner&#125;</span><br><span class="line">        <span class="tag">&lt;/<span class="name">if</span>&gt;</span></span><br><span class="line">    <span class="tag">&lt;/<span class="name">trim</span>&gt;</span></span><br><span class="line"><span class="tag">&lt;/<span class="name">select</span>&gt;</span></span><br></pre></td></tr></table></figure>

<p><code>trim</code>元素的主要功能是可以在自己<code>包含的内容前加上某些前缀</code>，也<code>可以在其后加上某些后缀</code>，与之对应的属性是<code>prefix</code>和<code>suffix</code>；可以把包含内容的首部某些内容覆盖，即忽略，也可以把尾部的某些内容覆盖，对应的属性是<code>prefixOverrides</code>和<code>suffixOverrides</code>；正因为trim有这样的功能，所以我们也可以非常简单的利用trim来代替where元素的功能。</p>
<h2 id="where语句"><a href="#where语句" class="headerlink" title="where语句"></a>where语句</h2><figure class="highlight xml"><table><tr><td class="code"><pre><span class="line"><span class="tag">&lt;<span class="name">select</span> <span class="attr">id</span>=<span class="string">"dynamicWhereTest"</span> <span class="attr">parameterType</span>=<span class="string">"Blog"</span> <span class="attr">resultType</span>=<span class="string">"Blog"</span>&gt;</span></span><br><span class="line">    select * from t_blog </span><br><span class="line">    <span class="tag">&lt;<span class="name">where</span>&gt;</span></span><br><span class="line">        <span class="tag">&lt;<span class="name">if</span> <span class="attr">test</span>=<span class="string">"title != null"</span>&gt;</span></span><br><span class="line">            title = #&#123;title&#125;</span><br><span class="line">        <span class="tag">&lt;/<span class="name">if</span>&gt;</span></span><br><span class="line">        <span class="tag">&lt;<span class="name">if</span> <span class="attr">test</span>=<span class="string">"content != null"</span>&gt;</span></span><br><span class="line">            and content = #&#123;content&#125;</span><br><span class="line">        <span class="tag">&lt;/<span class="name">if</span>&gt;</span></span><br><span class="line">        <span class="tag">&lt;<span class="name">if</span> <span class="attr">test</span>=<span class="string">"owner != null"</span>&gt;</span></span><br><span class="line">            and owner = #&#123;owner&#125;</span><br><span class="line">        <span class="tag">&lt;/<span class="name">if</span>&gt;</span></span><br><span class="line">    <span class="tag">&lt;/<span class="name">where</span>&gt;</span></span><br><span class="line"><span class="tag">&lt;/<span class="name">select</span>&gt;</span></span><br></pre></td></tr></table></figure>

<p><code>where</code>元素的作用是会在写入<code>where</code>元素的地方输出一个<code>where</code>，另外一个好处是你不需要考虑where元素里面的条件输出是什么样子的，MyBatis会智能的帮你处理，如果所有的条件都不满足那么MyBatis就会查出所有的记录，如果输出后是and 开头的，MyBatis会把第一个and忽略，当然如果是or开头的，MyBatis也会把它忽略；此外，在where元素中你不需要考虑空格的问题，MyBatis会智能的帮你加上。像上述例子中，如果title=null， 而content != null，那么输出的整个语句会是select * from t_blog where content = #{content}，而不是select * from t_blog where and content = #{content}，因为<code>MyBatis会智能的把首个and 或 or 给忽略</code>。</p>
<h2 id="set语句"><a href="#set语句" class="headerlink" title="set语句"></a>set语句</h2><figure class="highlight xml"><table><tr><td class="code"><pre><span class="line"><span class="tag">&lt;<span class="name">update</span> <span class="attr">id</span>=<span class="string">"dynamicSetTest"</span> <span class="attr">parameterType</span>=<span class="string">"Blog"</span>&gt;</span></span><br><span class="line">    update t_blog</span><br><span class="line">    <span class="tag">&lt;<span class="name">set</span>&gt;</span></span><br><span class="line">        <span class="tag">&lt;<span class="name">if</span> <span class="attr">test</span>=<span class="string">"title != null"</span>&gt;</span></span><br><span class="line">            title = #&#123;title&#125;,</span><br><span class="line">        <span class="tag">&lt;/<span class="name">if</span>&gt;</span></span><br><span class="line">        <span class="tag">&lt;<span class="name">if</span> <span class="attr">test</span>=<span class="string">"content != null"</span>&gt;</span></span><br><span class="line">            content = #&#123;content&#125;,</span><br><span class="line">        <span class="tag">&lt;/<span class="name">if</span>&gt;</span></span><br><span class="line">        <span class="tag">&lt;<span class="name">if</span> <span class="attr">test</span>=<span class="string">"owner != null"</span>&gt;</span></span><br><span class="line">            owner = #&#123;owner&#125;</span><br><span class="line">        <span class="tag">&lt;/<span class="name">if</span>&gt;</span></span><br><span class="line">    <span class="tag">&lt;/<span class="name">set</span>&gt;</span></span><br><span class="line">    where id = #&#123;id&#125;</span><br><span class="line"><span class="tag">&lt;/<span class="name">update</span>&gt;</span></span><br></pre></td></tr></table></figure>

<p><code>set</code>元素主要是<code>用在更新操作的时候</code>，它的主要功能和where元素其实是差不多的，主要是在包含的语句前输出一个set，然后<code>如果包含的语句是以逗号结束的话将会把该逗号忽略</code>，如果<code>set包含的内容为空的话则会出错</code>。有了set元素我们就可以动态的更新那些修改了的字段。</p>
<h2 id="foreach"><a href="#foreach" class="headerlink" title="foreach"></a>foreach</h2><p><code>foreach</code>的主要擢用在构建in条件中，它可以在SQL语句中进行迭代一个集合。<code>foreach元素的属性主要有item、index、collection、open、separator、close</code>。</p>
<ul>
<li><code>item</code>:表示集合中每一个元素进行迭代时的别名。</li>
<li><code>index</code>:指定一个名字，用于表示在迭代过程中，每次迭代到的位置。</li>
<li><code>open</code>:表示该语句什么时候可以开始。</li>
<li><code>separator</code>:表示在每一进行迭代之间以什么符号作为分隔符。</li>
<li><code>close</code>:表示以什么结束。</li>
</ul>
<p>在foreach的时候最关键的也是最容易出错的就是collection属性，该属性是必须指定的，但是在不同情况下，该属性的值是不一样的，主要有以下三种情况：</p>
<ol>
<li>如果传入的是单参数类型是一个<code>List</code>的时候，<code>collection</code>属性为<code>list</code></li>
<li>如果传入的是但参数类型是一个<code>array</code>数组的时候，<code>collection</code>属性值为<code>array</code></li>
<li>如果传入的参数是<code>多个</code>的时候，我们就需要把他们封装成一个<code>Map</code>了，当然参数也可以封装成<code>map</code>，实际上如果你在传入参数的时候，在MyBati里面也是会把它封装成一个Map的，map的key就是参数名，所以这个时候collection属性值就是传入List或array对象在自己封装的map里面的key</li>
</ol>
<h3 id="单参数List的类型"><a href="#单参数List的类型" class="headerlink" title="单参数List的类型"></a>单参数List的类型</h3><figure class="highlight xml"><table><tr><td class="code"><pre><span class="line"><span class="tag">&lt;<span class="name">select</span> <span class="attr">id</span>=<span class="string">"dynamicForeachTest"</span> <span class="attr">resultType</span>=<span class="string">"Blog"</span>&gt;</span></span><br><span class="line">      select * from t_blog where id in</span><br><span class="line">      <span class="tag">&lt;<span class="name">foreach</span> <span class="attr">collection</span>=<span class="string">"list"</span> <span class="attr">index</span>=<span class="string">"index"</span> <span class="attr">item</span>=<span class="string">"item"</span> <span class="attr">open</span>=<span class="string">"("</span> </span></span><br><span class="line"><span class="tag">      <span class="attr">separator</span>=<span class="string">","</span> <span class="attr">close</span>=<span class="string">")"</span>&gt;</span></span><br><span class="line">          #&#123;item&#125;</span><br><span class="line">      <span class="tag">&lt;/<span class="name">foreach</span>&gt;</span></span><br><span class="line">  <span class="tag">&lt;/<span class="name">select</span>&gt;</span></span><br></pre></td></tr></table></figure>

<p>上述collection的值为list，对应的Mapper是这样的</p>
<figure class="highlight java"><table><tr><td class="code"><pre><span class="line"><span class="function"><span class="keyword">public</span> List&lt;Blog&gt; <span class="title">dynamicForeachTest</span><span class="params">(List&lt;Integer&gt; ids)</span></span>;</span><br></pre></td></tr></table></figure>

<p>测试代码：</p>
<figure class="highlight java"><table><tr><td class="code"><pre><span class="line"><span class="function"><span class="keyword">public</span> <span class="keyword">void</span> <span class="title">dynamicForeachTest</span><span class="params">()</span> </span>&#123;</span><br><span class="line">    SqlSession session = Util.getSqlSessionFactory().openSession();</span><br><span class="line">    BlogMapper blogMapper = session.getMapper(BlogMapper<span class="class">.<span class="keyword">class</span>)</span>;</span><br><span class="line">    List&lt;Integer&gt; ids = <span class="keyword">new</span> ArrayList&lt;Integer&gt;();</span><br><span class="line">    ids.add(<span class="number">1</span>);</span><br><span class="line">    ids.add(<span class="number">3</span>);</span><br><span class="line">    ids.add(<span class="number">6</span>);</span><br><span class="line">    List&lt;Blog&gt; blogs = blogMapper.dynamicForeachTest(ids);</span><br><span class="line">    <span class="keyword">for</span>(Blog blog:blogs)</span><br><span class="line">        System.out.println(blog);</span><br><span class="line">    session.close();</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>

<h3 id="数组类型的参数"><a href="#数组类型的参数" class="headerlink" title="数组类型的参数"></a>数组类型的参数</h3><figure class="highlight xml"><table><tr><td class="code"><pre><span class="line"><span class="tag">&lt;<span class="name">select</span> <span class="attr">id</span>=<span class="string">"dynamicForeach2Test"</span> <span class="attr">resultType</span>=<span class="string">"Blog"</span>&gt;</span></span><br><span class="line">    select * from t_blog where id in</span><br><span class="line">    <span class="tag">&lt;<span class="name">foreach</span> <span class="attr">collection</span>=<span class="string">"array"</span> <span class="attr">index</span>=<span class="string">"index"</span> <span class="attr">item</span>=<span class="string">"item"</span> <span class="attr">open</span>=<span class="string">"("</span></span></span><br><span class="line"><span class="tag">     <span class="attr">separator</span>=<span class="string">","</span> <span class="attr">close</span>=<span class="string">")"</span>&gt;</span></span><br><span class="line">        #&#123;item&#125;</span><br><span class="line">    <span class="tag">&lt;/<span class="name">foreach</span>&gt;</span></span><br><span class="line"><span class="tag">&lt;/<span class="name">select</span>&gt;</span></span><br></pre></td></tr></table></figure>

<p>对应mapper</p>
<figure class="highlight java"><table><tr><td class="code"><pre><span class="line"><span class="function"><span class="keyword">public</span> List&lt;Blog&gt; <span class="title">dynamicForeach2Test</span><span class="params">(<span class="keyword">int</span>[] ids)</span></span>;</span><br></pre></td></tr></table></figure>

<h3 id="Map-类型的参数"><a href="#Map-类型的参数" class="headerlink" title="Map 类型的参数"></a>Map 类型的参数</h3><figure class="highlight xml"><table><tr><td class="code"><pre><span class="line"><span class="tag">&lt;<span class="name">select</span> <span class="attr">id</span>=<span class="string">"dynamicForeach3Test"</span> <span class="attr">resultType</span>=<span class="string">"Blog"</span>&gt;</span></span><br><span class="line">      select * from t_blog where title like "%"#&#123;title&#125;"%" and id in</span><br><span class="line">      <span class="tag">&lt;<span class="name">foreach</span> <span class="attr">collection</span>=<span class="string">"ids"</span> <span class="attr">index</span>=<span class="string">"index"</span> <span class="attr">item</span>=<span class="string">"item"</span> <span class="attr">open</span>=<span class="string">"("</span> </span></span><br><span class="line"><span class="tag">      <span class="attr">separator</span>=<span class="string">","</span> <span class="attr">close</span>=<span class="string">")"</span>&gt;</span></span><br><span class="line">          #&#123;item&#125;</span><br><span class="line">      <span class="tag">&lt;/<span class="name">foreach</span>&gt;</span></span><br><span class="line">  <span class="tag">&lt;/<span class="name">select</span>&gt;</span></span><br></pre></td></tr></table></figure>

<p>mapper接口</p>
<figure class="highlight java"><table><tr><td class="code"><pre><span class="line"><span class="function"><span class="keyword">public</span> List&lt;Blog&gt; <span class="title">dynamicForeach3Test</span><span class="params">(Map&lt;String,Object&gt; params)</span></span>;</span><br></pre></td></tr></table></figure>

<p>通过以上方法，就能完成一般的mybatis的动态sql。最常用的就是if where foreach这几个，一定要重点掌握。</p>

        </div>

        <blockquote class="post-copyright">
    <div class="content">
        
<span class="post-time">
    最后更新时间：<time datetime="2020-03-18T04:07:24.366Z" itemprop="dateUpdated">2020年3月18日 12:07</time>
</span><br>


        这里写留言或版权声明：<a href="/blog/2016/12/23/java/mybatis动态sql语句/" target="_blank" rel="external">http://windynature.oschina.io/2016/12/23/java/mybatis动态sql语句/</a>
    </div>
    <footer>
        <a href="http://windynature.oschina.io">
            <img src="/blog/img/user2.jpg" alt="windy">
            windy
        </a>
    </footer>
</blockquote>

        


        <div class="post-footer">
            
	<ul class="article-tag-list"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/blog/tags/ORM/">ORM</a></li><li class="article-tag-list-item"><a class="article-tag-list-link" href="/blog/tags/mybatis/">mybatis</a></li></ul>


            
<div class="page-share-wrap">
    

<div class="page-share" id="pageShare">
    <ul class="reset share-icons">
      <li>
        <a class="weibo share-sns" target="_blank" href="http://service.weibo.com/share/share.php?url=http://windynature.oschina.io/2016/12/23/java/mybatis动态sql语句/&title=《mybatis动态sql语句》 — 奔跑的蜗牛&pic=http://windynature.oschina.io/img/user2.jpg" data-title="微博">
          <i class="icon icon-weibo"></i>
        </a>
      </li>
      <li>
        <a class="weixin share-sns wxFab" href="javascript:;" data-title="微信">
          <i class="icon icon-weixin"></i>
        </a>
      </li>
      <li>
        <a class="qq share-sns" target="_blank" href="http://connect.qq.com/widget/shareqq/index.html?url=http://windynature.oschina.io/2016/12/23/java/mybatis动态sql语句/&title=《mybatis动态sql语句》 — 奔跑的蜗牛&source=" data-title=" QQ">
          <i class="icon icon-qq"></i>
        </a>
      </li>
      <li>
        <a class="facebook share-sns" target="_blank" href="https://www.facebook.com/sharer/sharer.php?u=http://windynature.oschina.io/2016/12/23/java/mybatis动态sql语句/" data-title=" Facebook">
          <i class="icon icon-facebook"></i>
        </a>
      </li>
      <li>
        <a class="twitter share-sns" target="_blank" href="https://twitter.com/intent/tweet?text=《mybatis动态sql语句》 — 奔跑的蜗牛&url=http://windynature.oschina.io/2016/12/23/java/mybatis动态sql语句/&via=http://windynature.oschina.io" data-title=" Twitter">
          <i class="icon icon-twitter"></i>
        </a>
      </li>
      <li>
        <a class="google share-sns" target="_blank" href="https://plus.google.com/share?url=http://windynature.oschina.io/2016/12/23/java/mybatis动态sql语句/" data-title=" Google+">
          <i class="icon icon-google-plus"></i>
        </a>
      </li>
    </ul>
 </div>



    <a href="javascript:;" id="shareFab" class="page-share-fab waves-effect waves-circle">
        <i class="icon icon-share-alt icon-lg"></i>
    </a>
</div>



        </div>
    </div>

    
<nav class="post-nav flex-row flex-justify-between">
  
    <div class="waves-block waves-effect prev">
      <a href="/blog/2017/01/25/设计模式/设计模式概论/" id="post-prev" class="post-nav-link">
        <div class="tips"><i class="icon icon-angle-left icon-lg icon-pr"></i> Prev</div>
        <h4 class="title">设计模式概论</h4>
      </a>
    </div>
  

  
    <div class="waves-block waves-effect next">
      <a href="/blog/2016/10/04/java/动态代理/" id="post-next" class="post-nav-link">
        <div class="tips">Next <i class="icon icon-angle-right icon-lg icon-pl"></i></div>
        <h4 class="title">动态代理机制详解</h4>
      </a>
    </div>
  
</nav>



    

<div class="comments" id="comments">
    <div class="ds-thread" data-thread-key="java/mybatis动态sql语句" data-title="mybatis动态sql语句" data-url="http://windynature.oschina.io/2016/12/23/java/mybatis动态sql语句/"></div>
</div>
<script>
lazyScripts.push('//cdn.bootcss.com/marked/0.3.6/marked.min.js');

var duoshuoQuery = {short_name:'ysblog', theme: 'none'};
lazyScripts.push('//unpkg.com/hexo-theme-material-indigo@1.5.2/js/embed.min.js');


</script>










</article>



</div>

        <footer class="footer">
    <div class="top">
        
<p>
    <span id="busuanzi_container_site_uv" style='display:none'>
        站点总访客数：<span id="busuanzi_value_site_uv"></span>
    </span>
    <span id="busuanzi_container_site_pv" style='display:none'>
        站点总访问量：<span id="busuanzi_value_site_pv"></span>
    </span>
</p>


        <p>
            <span><a href="/atom.xml" target="_blank" class="rss" title="rss"><i class="icon icon-lg icon-rss"></i></a></span>
            <span>博客内容遵循 <a href="http://creativecommons.org/licenses/by-nc-sa/4.0/" target="_blank">知识共享 署名 - 非商业性 - 相同方式共享 4.0协议</a></span>
        </p>
    </div>
    <div class="bottom">
        <p>
            <span>Power by <a href="http://hexo.io/" target="_blank">Hexo</a> Theme <a href="https://github.com/yscoder/hexo-theme-indigo" target="_blank">indigo</a></span>
            <span>奔跑的蜗牛 &copy; 2015 - 2020</span>
        </p>
    </div>
</footer>

    </main>
    <div class="mask" id="mask"></div>
<a href="javascript:;" id="gotop" class="waves-effect waves-circle waves-light"><span class="icon icon-lg icon-chevron-up"></span></a>



<div class="global-share" id="globalShare">
    <ul class="reset share-icons">
      <li>
        <a class="weibo share-sns" target="_blank" href="http://service.weibo.com/share/share.php?url=http://windynature.oschina.io/2016/12/23/java/mybatis动态sql语句/&title=《mybatis动态sql语句》 — 奔跑的蜗牛&pic=http://windynature.oschina.io/img/user2.jpg" data-title="微博">
          <i class="icon icon-weibo"></i>
        </a>
      </li>
      <li>
        <a class="weixin share-sns wxFab" href="javascript:;" data-title="微信">
          <i class="icon icon-weixin"></i>
        </a>
      </li>
      <li>
        <a class="qq share-sns" target="_blank" href="http://connect.qq.com/widget/shareqq/index.html?url=http://windynature.oschina.io/2016/12/23/java/mybatis动态sql语句/&title=《mybatis动态sql语句》 — 奔跑的蜗牛&source=" data-title=" QQ">
          <i class="icon icon-qq"></i>
        </a>
      </li>
      <li>
        <a class="facebook share-sns" target="_blank" href="https://www.facebook.com/sharer/sharer.php?u=http://windynature.oschina.io/2016/12/23/java/mybatis动态sql语句/" data-title=" Facebook">
          <i class="icon icon-facebook"></i>
        </a>
      </li>
      <li>
        <a class="twitter share-sns" target="_blank" href="https://twitter.com/intent/tweet?text=《mybatis动态sql语句》 — 奔跑的蜗牛&url=http://windynature.oschina.io/2016/12/23/java/mybatis动态sql语句/&via=http://windynature.oschina.io" data-title=" Twitter">
          <i class="icon icon-twitter"></i>
        </a>
      </li>
      <li>
        <a class="google share-sns" target="_blank" href="https://plus.google.com/share?url=http://windynature.oschina.io/2016/12/23/java/mybatis动态sql语句/" data-title=" Google+">
          <i class="icon icon-google-plus"></i>
        </a>
      </li>
    </ul>
 </div>


<div class="page-modal wx-share" id="wxShare">
    <a class="close" href="javascript:;"><i class="icon icon-close"></i></a>
    <p>扫一扫，分享到微信</p>
    <img src="" alt="微信分享二维码">
</div>




    <script src="//cdn.bootcss.com/node-waves/0.7.4/waves.min.js"></script>
<script>
var BLOG = { ROOT: '/blog/', SHARE: true, REWARD: false };



</script>

<script src="//unpkg.com/hexo-theme-material-indigo@1.5.2/js/main.min.js"></script>


<div class="search-panel" id="search-panel">
    <ul class="search-result" id="search-result"></ul>
</div>
<template id="search-tpl">
<li class="item">
    <a href="{path}" class="waves-block waves-effect">
        <div class="title ellipsis" title="{title}">{title}</div>
        <div class="flex-row flex-middle">
            <div class="tags ellipsis">
                {tags}
            </div>
            <time class="flex-col time">{date}</time>
        </div>
    </a>
</li>
</template>

<script src="//unpkg.com/hexo-theme-material-indigo@1.5.2/js/search.min.js" async></script>






<script async src="//dn-lbstatics.qbox.me/busuanzi/2.3/busuanzi.pure.mini.js"></script>





</body>
</html>
